#-*- coding: UTF-8 -*-
import MySQLdb
import MySQLdb.cursors
import datetime
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

conn= MySQLdb.connect(
    host='127.0.0.1',
    port = 3306,
    user='root',
    passwd='01827746521',
    db ='fof',
    cursorclass = MySQLdb.cursors.DictCursor,
    charset="utf8"
)
cur = conn.cursor()

def getAllAssetStrategy():
    getAllAssetStrategySql = "select id,windInvestStyle,windInvestType,goFundTypeTarget,goFundTypeStrategy,goFundTypeQuant,goFundTypeHedging from fof_asset_strategy_info WHERE type = 1";
    cur.execute(getAllAssetStrategySql);
    return cur.fetchall()


allAssetStrategy = getAllAssetStrategy();

for assetStrategy in allAssetStrategy:
    defaultAssetStrategyId = assetStrategy['id']
    fundTypeTarget = assetStrategy['goFundTypeTarget'] + "," + assetStrategy['windInvestType']
    fundTypeStrategy = assetStrategy['goFundTypeStrategy']
    fundTypeQuant = assetStrategy['goFundTypeQuant']
    fundTypeHedging = assetStrategy['goFundTypeHedging']

    updateSql = "update fund_info set defaultAssetStrategyId = " + str(defaultAssetStrategyId) + " where 1=1 and ("

    for item in fundTypeTarget.split(","):
        if item == '':
            continue
        updateSql += " fund_type_target like '%" + item + "%' or"

    for item in fundTypeStrategy.split(","):
        if item == '':
            continue
        updateSql += " fund_type_strategy like '%" + item + "%' or"

    for item in fundTypeQuant.split(","):
        if item == '':
            continue
        updateSql += " fund_type_quant = '" + item + "' or"

    for item in fundTypeHedging.split(","):
        if item == '':
            continue
        updateSql += " fund_type_hedging = '" + item + "' or"

    if(updateSql.endswith('or')):
        updateSql = updateSql[0:len(updateSql)-2]

    updateSql += ")"
    print updateSql

    cur.execute(updateSql)
    conn.commit()

updateSql = "update fund_info set defaultAssetStrategyId = 2 where yield1y >=0 and yield1y <= 0.1 and ABS(drawdown) <= 0.02 and ABS(volatilityAnnually) <= 0.02"
cur.execute(updateSql)
conn.commit()

#用以下SQL 查询出分级A的列表，如果基金代码落入该结果集，则分类为债券型基金：select f_info_feeder_windcode from CHINAGRADINGFUND where f_info_feeder_typecode = 614001001
#则分类为固定收益型基金
updateSql = "UPDATE fund_info a right join CHINAGRADINGFUND b ON a.fund_ext_code = b.f_info_feeder_windcode SET a.defaultAssetStrategyId = 2 where b.f_info_feeder_typecode = 614001001"
cur.execute(updateSql)
conn.commit()

#权益类中（assetStrategyId=1）年化收益率：0-10%, |最大回撤| <0.2%, |年化波动率| < 0.2%
updateSql = "update fund_info SET defaultAssetStrategyId = 2 where abs(yieldAnnually) <= 0.1 and abs(drawdown) <= 0.002 and abs(volatilityAnnually) <= 0.002 and defaultAssetStrategyId = 1"
cur.execute(updateSql)
conn.commit()

cur.close()
conn.close()
